package com.metagurukul.metaboard.dao;

import java.util.*;
import java.sql.*;

import com.metagurukul.metaboard.model.notification.*;

public class NotificationDAO 
{
	Connection con = ConnectionFactory.getConnection();
	
	public boolean post(Notification notification)
	{
		int rows = -1;
		String query = "INSERT INTO notifications(title, description, creator_id, section_id, expiry_date, archived) VALUES (?, ?, ?, ?, ?, ?) ";
		int notificationID = notification.getNotificationID();
		String title = notification.getTitle();
		String description = notification.getDescription();
		int creatorID = notification.getCreatorID();
		int sectionID = notification.getSectionID();
		java.sql.Date expiryDate = notification.getExpiryDate();
		int archived = notification.getArchived();
		
		PreparedStatement ps = null;
		 
		try
		{
			ps = con.prepareStatement(query);
			ps.setString(1, title);
			ps.setString(2, description);
			ps.setInt(3, creatorID);
			ps.setInt(4, sectionID);
			ps.setDate(5, expiryDate);
			ps.setInt(6, archived);
			rows = ps.executeUpdate();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
				/* close connection */
			try 
			{
				//ConnectionFactory.closeConnection();
				if (ps != null) 
				{
					ps.close();
				}
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(rows != 0 )
			return true;
		else 
			return false;	 
	}
	 
	public boolean remove(int notificationID)
	{
		int rows = -1;
		String query = "UPDATE notifications SET archived = ? WHERE n_id = ?";
		PreparedStatement ps = null;
		try
		{
			 ps = con.prepareStatement(query);
			 ps.setBoolean(1, true);
			 ps.setInt(2, notificationID);
			 rows = ps.executeUpdate();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			/* close connection */
			try 
			{
				//ConnectionFactory.closeConnection();
				if (ps != null) 
				{
					ps.close();
				}
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(rows != 0)
			return true;
		else 
			return false;		 
	 }
	 
	public boolean delete(int notificationID)
	{
		int rows = -1;
		String query = "DELETE FROM notifications where n_id = ?";
		PreparedStatement ps = null;
		try
		{
			ps = con.prepareStatement(query);
			ps.setInt(1, notificationID);
			rows = ps.executeUpdate();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			/* close connection */
			try 
			{
				//ConnectionFactory.closeConnection();
				if (ps != null) 
				{
					ps.close();
				}
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(rows != 0)
			return true;
		else 
			return false;		 
	 }
	 
	public boolean update(int notificationID, Notification notification)
	{
		int rows=-1;
		String query = "UPDATE notifications SET title = ?, description = ?, creator_id = ?, posted_time = ?, section_id = ?, expiry_dt = ?, archived = ? WHERE n_id = ?";
		String title = notification.getTitle();
		String description = notification.getDescription();
		int creator_id = notification.getCreatorID();
		java.sql.Date posted_time = notification.getPostedTime();
		int section_id = notification.getSectionID();
		java.sql.Date expiry_dt = notification.getExpiryDate();
		int archived = notification.getArchived();
		PreparedStatement ps = null;
		try
		{
			ps = con.prepareStatement(query);
			ps.setString(1, title);
			ps.setString(2, description);
			ps.setInt(3, creator_id);
			ps.setDate(4, posted_time);
			ps.setInt(5, section_id);
			ps.setDate(6, expiry_dt);
			ps.setInt(7, archived);
			ps.setInt(8, notificationID);
			rows = ps.executeUpdate();
		}
		catch (SQLException e) 
		{
				e.printStackTrace();
		} 
		finally
		{
			/* close connection */
			try 
			{
				//ConnectionFactory.closeConnection();
				if (ps != null) 
				{
					ps.close();
				}
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(rows != 0)
			return true;
		else 
			return false;	 
	 }
	 
	public ArrayList searchByTitle(String title)
	{
		List<Notification> notificationList = new ArrayList<Notification>();
		String query = "SELECT * FROM notification WHERE title = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try 
		{
			ps = con.prepareStatement(query);
			ps.setString(1,title);
			rs = ps.executeQuery();

			if(rs.next())
			{
				rs.beforeFirst();
				while (rs.next()) 
				{
					Notification notification = new Notification();
					notification.setNotificationID(rs.getInt(1));
					notification.setTitle(rs.getString(2)); 
					notification.setDescription(rs.getString(3));
					notification.setCreatorID(rs.getInt(4));
					notification.setPostedTime(rs.getDate(5));
					notification.setSectionID(rs.getInt(6));	
					notification.setExpiryDate(rs.getDate(7));
					notification.setArchived(rs.getInt(8));
					notificationList.add(notification);
				}
			}
			else
				return null;
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		finally
		{
			/* close connection */
			try 
			{
				//ConnectionFactory.closeConnection();
				if (rs != null) 
				{
					rs.close();
				}
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return (ArrayList) notificationList;
	 }
	
	
	public ArrayList searchByDate(java.sql.Date postedTime)
	{
		List<Notification> notificationList = new ArrayList<Notification>();
		String query = "SELECT * FROM notification WHERE date(posted_time) = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try 
		{
			ps = con.prepareStatement(query);
			ps.setDate(1,postedTime);
			rs = ps.executeQuery(query);

			if(rs.next())
			{
				rs.beforeFirst();
				while (rs.next()) 
				{
					Notification notification = new Notification();
					notification.setNotificationID(rs.getInt(1));
					notification.setTitle(rs.getString(2)); 
					notification.setDescription(rs.getString(3));
					notification.setCreatorID(rs.getInt(4));
					notification.setPostedTime(rs.getDate(5));
					notification.setSectionID(rs.getInt(6));	
					notification.setExpiryDate(rs.getDate(7));
					notification.setArchived(rs.getInt(8));
					notificationList.add(notification);
				}
			}
			else
				return null;
		}
		catch (SQLException e) 
		{
				e.printStackTrace();
		} 
		finally 
		{
				/* close connection */
			try 
			{
				//ConnectionFactory.closeConnection();
				if (ps != null) 
				{
					ps.close();
				}
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return (ArrayList) notificationList;
	 }
	 
	public ArrayList searchByCreator(String creatorName)
	{
		List<Notification> notificationList=new ArrayList<Notification>();
		String query = "SELECT * FROM notification WHERE creator_id = (select m_id FROM members WHERE name LIKE '%?%')";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try 
		{
		 	ps = con.prepareStatement(query);
		 	ps.setString(1,creatorName);
			rs = ps.executeQuery(query);

			if(rs.next())
			{
				rs.beforeFirst();
				while (rs.next()) 
				{
					Notification notification = new Notification();
					notification.setNotificationID(rs.getInt(1));
					notification.setTitle(rs.getString(2)); 
					notification.setDescription(rs.getString(3));
					notification.setCreatorID(rs.getInt(4));
					notification.setPostedTime(rs.getDate(5));
					notification.setSectionID(rs.getInt(6));	
					notification.setExpiryDate(rs.getDate(7));
					notification.setArchived(rs.getInt(8));
					notificationList.add(notification);
				}
			}
			else
				return null;
		 }
		catch (SQLException e) 
		{
				e.printStackTrace();
		} 
		finally 
		{
			/* close connection */
			try 
			{
				//ConnectionFactory.closeConnection();
				if (rs != null) 
				{
					rs.close();
				}
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return (ArrayList) notificationList;
	}
	
	public ArrayList show(int sectionID)
	{
		List<Notification> notificationList=new ArrayList<Notification>();
		String query = "SELECT * FROM notification WHERE section_id = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try 
		{
		 	ps = con.prepareStatement(query);
		 	ps.setInt(1,sectionID);
			rs = ps.executeQuery(query);

			if(rs.next())
			{
				rs.beforeFirst();
				while (rs.next()) 
				{
					Notification notification = new Notification();
					notification.setNotificationID(rs.getInt(1));
					notification.setTitle(rs.getString(2)); 
					notification.setDescription(rs.getString(3));
					notification.setCreatorID(rs.getInt(4));
					notification.setPostedTime(rs.getDate(5));
					notification.setSectionID(rs.getInt(6));	
					notification.setExpiryDate(rs.getDate(7));
					notification.setArchived(rs.getInt(8));
					notificationList.add(notification);
				}
			}
			else
				return null;
		 }
		catch (SQLException e) 
		{
				e.printStackTrace();
		} 
		finally 
		{
			/* close connection */
			try 
			{
				//ConnectionFactory.closeConnection();
				if (rs != null) 
				{
					rs.close();
				}
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return (ArrayList) notificationList;
	}
}